﻿CREATE PROCEDURE UpdateInstalledAddinsForUser
	@iid uniqueidentifier
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	declare @lastsubmit datetime = (
		select top 1 [Submission]
		from [UserInstalledAddins]
		where [InstanceId]=@iid
		order by [Submission] desc
		);

	merge [UserInstalledAddinsLatest] t
	using (select * from [UserInstalledAddins] where [InstanceId]=@iid and [Submission]=@lastsubmit) s
	on t.[InstanceId]=s.[InstanceId]
	and t.[InstanceId]=@iid
	and t.[ListIndex]=s.[ListIndex]
	when matched then
		update set [Submission]=s.[Submission], [ListIndex]=s.[ListIndex],
			[ProductCode]=s.[ProductCode], [Author]=s.[Author],
			[Title]=s.[Title], [Version]=s.[Version]
	when not matched by source and t.[InstanceId]=@iid then
		delete
	when not matched by target then
		insert ([InstanceId],[Submission],[ListIndex],[ProductCode],[Author],[Title],[Version])
		values (s.[InstanceId],s.[Submission],s.[ListIndex],s.[ProductCode],s.[Author],s.[Title],s.[Version])
	;
END
GO
